<?php
/**
 * DBShop 电子商务系统
 *
 * ==========================================================================
 * @link      https://www.dbshop.net/
 * @copyright 北京珑大钜商科技有限公司，并保留所有权利。
 * @license   https://www.dbshop.net/license.html License
 * ==========================================================================
 *
 * @author    静静的风 <baron@loongdom.cn>
 *
 */

namespace Sales\Repository;

use Admin\Data\Common;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;
use Sales\Entity\Order;
use Sales\Repository\QuerySearch\OrderSearch;
use User\Entity\User;
use function Doctrine\ORM\QueryBuilder;

class OrderRepository extends EntityRepository
{
    /**
     * 订单列表
     * @param array $search
     * @return \Doctrine\ORM\Query
     */
    public function findOrderList($search = [])
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('o')
            ->from(Order::class, 'o')
            ->orderBy('o.orderId', 'DESC');

        $query = OrderSearch::querySearchData($search, $query);

        return $query->getQuery();
    }

    /**
     * 订单支付记录
     * @param array $search
     * @return \Doctrine\ORM\Query
     */
    public function findOrderPaymentLog($search = [])
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('o')
            ->from(Order::class, 'o')
            ->where('o.paymentType = :paymentType')->setParameter('paymentType', 'OnlinePay')
            ->andWhere($query->expr()->isNotNull('o.paymentCode'))
            ->orderBy('o.orderId', 'DESC');

        $query = OrderSearch::querySearchData($search, $query);

        return $query->getQuery();
    }

    /**
     * 订单数量统计
     * @param string $startTime
     * @param string $endTime
     * @return int|mixed|string
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findOrderCount($startTime = '', $endTime = '')
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('COUNT(o.orderId)')->from(Order::class, 'o')
            ->where('o.orderStatus >= :orderStatus')->setParameter('orderStatus', Common::orderStatusCode('WAIT_PAYMENT'));
        if (!empty($startTime)) $query->andWhere($query->expr()->gte('o.orderAddTime', ':startTime'))->setParameter('startTime', $startTime);
        if (!empty($endTime))   $query->andWhere($query->expr()->lte('o.orderAddTime', ':endTime'))->setParameter('endTime', $endTime);

        return $query->getQuery()->getSingleScalarResult();
    }

    /**
     * 订单销售额统计
     * @param string $startTime
     * @param string $endTime
     * @return int|mixed|string
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findOrderTotal($startTime = '', $endTime = '')
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('SUM(o.baseOrderAmount)')->from(Order::class, 'o')
            ->where(
                $query->expr()->orX(
                    $query->expr()->andX()->add($query->expr()->eq('o.paymentType', ':onlinePaymentType'))->add($query->expr()->gte('o.orderStatus', ':finishPayState')),
                    $query->expr()->andX()->add($query->expr()->eq('o.paymentType', ':paymentType'))->add($query->expr()->gte('o.orderStatus', ':finishState'))
                )
            )
            ->setParameter('onlinePaymentType', 'OnlinePay')->setParameter('finishPayState', Common::orderStatusCode('WAIT_GOODS_ARRIVE'))
            ->setParameter('paymentType', 'CashOnDelivery')->setParameter('finishState', Common::orderStatusCode('GOODS_RECEIVED'));
        if (!empty($startTime)) $query->andWhere($query->expr()->gte('o.orderAddTime', ':startTime'))->setParameter('startTime', $startTime);
        if (!empty($endTime))   $query->andWhere($query->expr()->lte('o.orderAddTime', ':endTime'))->setParameter('endTime', $endTime);

        return number_format($query->getQuery()->getSingleScalarResult(), 2, '.', '');
    }

    /**
     * 根据时间分组统计订单数或者订单销售额
     * @param $startTime
     * @param $endTime
     * @param int $userGroupId
     * @param string $selectType count 统计订单数，total 统计订单销售额
     * @param false $paymentState
     * @param string $dateType
     * @return mixed[]
     * @throws \Doctrine\DBAL\DBALException
     */
    public function findOrderDateCountOrTotal($startTime, $endTime, $userGroupId = 0, $paymentState = false, $selectType = 'count', $dateType = '%Y-%m-%d')
    {
        $query  = $this->getEntityManager()->getConnection();
        if ($selectType == 'count') $sql = "SELECT COUNT(o.order_id) AS orderNum,";
        else $sql = "SELECT SUM(o.base_order_amount) AS orderTotal,";

        $sql    .= " FROM_UNIXTIME(o.order_add_time, '".$dateType."') AS userDate";
        $sql    .= " FROM dbshop_order AS o LEFT JOIN dbshop_user AS u ON u.user_id = o.user_id";
        $sql    .= " WHERE o.order_add_time >= ".$startTime;
        $sql    .= " AND o.order_add_time <= ".$endTime;
        if ($paymentState) {
            $sql .= " AND ((o.payment_type = 'OnlinePay' AND o.order_status >= ".Common::orderStatusCode('WAIT_GOODS_ARRIVE').") OR (o.payment_type = 'CashOnDelivery' AND o.order_status >= ".Common::orderStatusCode('GOODS_RECEIVED')."))";
        } else {
            $sql .= " AND o.order_status >= ".Common::orderStatusCode('WAIT_PAYMENT');
        }
        if ($userGroupId > 0) $sql .= ' AND u.user_group_id = '.$userGroupId;
        $sql    .= " GROUP BY userDate";

        $stmt   = $query->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * 统计支付方式的订单
     * @param $startTime
     * @param $endTime
     * @param int $userGroupId
     * @return int|mixed|string
     */
    public function findOrderCountPayment($startTime, $endTime, $userGroupId = 0)
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('COUNT(o.orderId) AS orderNum', 'o.paymentCode')->from(Order::class, 'o')
            ->leftJoin(User::class, 'u', Join::WITH, 'o.userId=u.userId')
            ->where('o.orderStatus >= :orderStatus')->setParameter('orderStatus', Common::orderStatusCode('WAIT_PAYMENT'))
            ->andWhere($query->expr()->isNotNull('o.paymentCode'));

        if ($userGroupId > 0)   $query->andWhere('u.userGroupId = :userGroupId')->setParameter('userGroupId', $userGroupId);
        if (!empty($startTime)) $query->andWhere($query->expr()->gte('o.orderAddTime', ':startTime'))->setParameter('startTime', $startTime);
        if (!empty($endTime))   $query->andWhere($query->expr()->lte('o.orderAddTime', ':endTime'))->setParameter('endTime', $endTime);
        $query->groupBy('o.paymentCode');

        return $query->getQuery()->getResult();
    }

    /**
     * 统计配送类型的订单
     * @param $startTime
     * @param $endTime
     * @param int $userGroupId
     * @return int|mixed|string
     */
    public function findOrderCountDelivery($startTime, $endTime, $userGroupId = 0)
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('COUNT(o.orderId) AS orderNum', 'o.deliveryType')->from(Order::class, 'o')
            ->leftJoin(User::class, 'u', Join::WITH, 'o.userId=u.userId')
            ->where('o.orderStatus >= :orderStatus')->setParameter('orderStatus', Common::orderStatusCode('WAIT_PAYMENT'))
            ->andWhere($query->expr()->isNotNull('o.deliveryType'));

        if ($userGroupId > 0)   $query->andWhere('u.userGroupId = :userGroupId')->setParameter('userGroupId', $userGroupId);
        if (!empty($startTime)) $query->andWhere($query->expr()->gte('o.orderAddTime', ':startTime'))->setParameter('startTime', $startTime);
        if (!empty($endTime))   $query->andWhere($query->expr()->lte('o.orderAddTime', ':endTime'))->setParameter('endTime', $endTime);
        $query->groupBy('o.deliveryType');

        return $query->getQuery()->getResult();
    }
    /*=======================================下面为前台使用=======================================*/
    /**
     * 获取对应会员的订单列表
     * @param $userId
     * @param array $search
     * @return \Doctrine\ORM\Query
     */
    public function shopFindMyOrderList($userId, $search = [])
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('o')
            ->from(Order::class, 'o')
            ->where('o.userId = :userId')->setParameter('userId', $userId)
            ->orderBy('o.orderId', 'DESC');

        $query = OrderSearch::shopQuerySearchData($search, $query);

        return $query->getQuery();
    }

}